import pymysql

# 数据库连接配置（替换成你的实际信息）
DB_CONFIG = {
    'host': '127.0.0.1',  # 或你的MySQL服务器地址
    'port': 3306,         # 默认MySQL端口
    'user': 'subeplayreal',
    'password': '51522ZzWlWlBb',
    'db': 'subeplayreal',
    'charset': 'utf8mb4'
}

# 获取数据库连接
def get_conn():
    return pymysql.connect(**DB_CONFIG)

# 测试连接
def test_connection():
    conn = get_conn()
    try:
        with conn.cursor() as cursor:
            cursor.execute("SELECT 1")  # 简单查询
            result = cursor.fetchone()
            print("数据库连接成功:", result)
    except Exception as e:
        print("数据库连接失败:", e)
    finally:
        conn.close()

# 创建WebSocket聊天相关的数据库表
def create_chat_tables():
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()
    
    try:
        # 创建聊天消息表
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS chat_messages (
            message_id INT AUTO_INCREMENT PRIMARY KEY,
            sender_id INT NOT NULL,
            receiver_id INT NOT NULL,
            content TEXT NOT NULL,
            msg_type VARCHAR(20) DEFAULT 'text',
            is_read BOOLEAN DEFAULT FALSE,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_sender (sender_id),
            INDEX idx_receiver (receiver_id),
            INDEX idx_created_at (created_at),
            FOREIGN KEY (sender_id) REFERENCES users(userid) ON DELETE CASCADE,
            FOREIGN KEY (receiver_id) REFERENCES users(userid) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        """)
        
        # 创建聊天会话表
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS chat_sessions (
            session_id INT AUTO_INCREMENT PRIMARY KEY,
            user1_id INT NOT NULL,
            user2_id INT NOT NULL,
            last_message_id INT,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY unique_session (user1_id, user2_id),
            INDEX idx_user1 (user1_id),
            INDEX idx_user2 (user2_id),
            INDEX idx_updated_at (updated_at),
            FOREIGN KEY (user1_id) REFERENCES users(userid) ON DELETE CASCADE,
            FOREIGN KEY (user2_id) REFERENCES users(userid) ON DELETE CASCADE,
            FOREIGN KEY (last_message_id) REFERENCES chat_messages(message_id) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        """)
        
        # 创建用户在线状态表
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS user_online_status (
            user_id INT PRIMARY KEY,
            is_online BOOLEAN DEFAULT FALSE,
            last_active_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_is_online (is_online),
            INDEX idx_last_active (last_active_at),
            FOREIGN KEY (user_id) REFERENCES users(userid) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        """)
        
        conn.commit()
        print("聊天相关数据库表创建成功")
    except Exception as e:
        conn.rollback()
        print(f"创建聊天数据库表时出错: {e}")
    finally:
        cursor.close()
        conn.close()





# 创建WebSocket群聊相关的数据库表
def create_chat_group_tables():
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()
    
    try:
        # 创建群组表
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS chat_groups (
            group_id INT AUTO_INCREMENT PRIMARY KEY,
            group_name VARCHAR(100) NOT NULL,
            group_avatar VARCHAR(255),
            creator_id INT NOT NULL,
            description TEXT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_creator (creator_id),
            FOREIGN KEY (creator_id) REFERENCES users(userid) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        """)
        
        # 创建群组成员表
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS chat_group_members (
            id INT AUTO_INCREMENT PRIMARY KEY,
            group_id INT NOT NULL,
            user_id INT NOT NULL,
            nickname VARCHAR(50),
            role ENUM('admin', 'member') DEFAULT 'member',
            joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY unique_member (group_id, user_id),
            INDEX idx_group (group_id),
            INDEX idx_user (user_id),
            FOREIGN KEY (group_id) REFERENCES chat_groups(group_id) ON DELETE CASCADE,
            FOREIGN KEY (user_id) REFERENCES users(userid) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        """)
        
        # 创建群聊消息表
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS chat_group_messages (
            message_id INT AUTO_INCREMENT PRIMARY KEY,
            group_id INT NOT NULL,
            sender_id INT NOT NULL,
            content TEXT NOT NULL,
            msg_type VARCHAR(20) DEFAULT 'text',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_group (group_id),
            INDEX idx_sender (sender_id),
            INDEX idx_created_at (created_at),
            FOREIGN KEY (group_id) REFERENCES chat_groups(group_id) ON DELETE CASCADE,
            FOREIGN KEY (sender_id) REFERENCES users(userid) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        """)
        
        # 创建群聊消息读取状态表
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS chat_group_message_status (
            id INT AUTO_INCREMENT PRIMARY KEY,
            message_id INT NOT NULL,
            user_id INT NOT NULL,
            is_read BOOLEAN DEFAULT FALSE,
            read_at DATETIME,
            UNIQUE KEY unique_status (message_id, user_id),
            INDEX idx_message (message_id),
            INDEX idx_user (user_id),
            INDEX idx_is_read (is_read),
            FOREIGN KEY (message_id) REFERENCES chat_group_messages(message_id) ON DELETE CASCADE,
            FOREIGN KEY (user_id) REFERENCES users(userid) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        """)
        
        conn.commit()
        print("群聊相关数据库表创建成功")
    except Exception as e:
        conn.rollback()
        print(f"创建群聊数据库表时出错: {e}")
    finally:
        cursor.close()
        conn.close()








# 插入一些测试聊天消息数据
def insert_sample_chat_data():
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()
    
    try:
        # 先检查是否有用户数据
        cursor.execute("SELECT userid FROM users LIMIT 6")
        users = cursor.fetchall()
        
        if len(users) < 2:
            print("用户数据不足，请先插入足够的用户数据")
            return
        
        # 插入一些测试消息
        user1_id = users[0][0]
        user2_id = users[1][0]
        user3_id = users[2][0] if len(users) > 2 else user1_id
        
        # 插入聊天消息
        messages = [
            (user1_id, user2_id, "你好，最近怎么样？", "text", True, "2023-11-01 10:00:00"),
            (user2_id, user1_id, "我很好，谢谢关心！你呢？", "text", True, "2023-11-01 10:01:30"),
            (user1_id, user2_id, "我也不错，最近在忙什么？", "text", True, "2023-11-01 10:03:45"),
            (user2_id, user1_id, "在准备一个新项目，很有挑战性。", "text", True, "2023-11-01 10:05:20"),
            (user1_id, user3_id, "嗨，周末有空一起吃饭吗？", "text", False, "2023-11-02 14:30:00"),
            (user3_id, user1_id, "好啊，几点？在哪里？", "text", True, "2023-11-02 14:45:10"),
            (user2_id, user3_id, "听说你最近换了新工作？", "text", False, "2023-11-03 09:10:00"),
        ]
        
        message_ids = []
        for sender_id, receiver_id, content, msg_type, is_read, created_at in messages:
            cursor.execute("""
            INSERT INTO chat_messages (sender_id, receiver_id, content, msg_type, is_read, created_at)
            VALUES (%s, %s, %s, %s, %s, %s)
            """, (sender_id, receiver_id, content, msg_type, is_read, created_at))
            message_ids.append(cursor.lastrowid)
        
        # 创建聊天会话
        sessions = [
            (user1_id, user2_id, message_ids[3], "2023-11-01 10:05:20"),  # 最后一条消息是第4条
            (user1_id, user3_id, message_ids[5], "2023-11-02 14:45:10"),  # 最后一条消息是第6条
            (user2_id, user3_id, message_ids[6], "2023-11-03 09:10:00"),  # 最后一条消息是第7条
        ]
        
        for user1_id, user2_id, last_message_id, updated_at in sessions:
            # 确保user1_id小于user2_id，保持一致性
            if user1_id > user2_id:
                user1_id, user2_id = user2_id, user1_id
                
            cursor.execute("""
            INSERT INTO chat_sessions (user1_id, user2_id, last_message_id, updated_at, created_at)
            VALUES (%s, %s, %s, %s, %s)
            ON DUPLICATE KEY UPDATE last_message_id = %s, updated_at = %s
            """, (
                user1_id, user2_id, last_message_id, updated_at, updated_at,
                last_message_id, updated_at
            ))
        
        # 更新用户在线状态
        online_status = [
            (user1_id, True, "2023-11-03 15:00:00"),
            (user2_id, False, "2023-11-03 12:30:00"),
            (user3_id, True, "2023-11-03 14:45:00"),
        ]
        
        for user_id, is_online, last_active_at in online_status:
            cursor.execute("""
            INSERT INTO user_online_status (user_id, is_online, last_active_at)
            VALUES (%s, %s, %s)
            ON DUPLICATE KEY UPDATE is_online = %s, last_active_at = %s
            """, (
                user_id, is_online, last_active_at,
                is_online, last_active_at
            ))
        
        conn.commit()
        print("聊天测试数据插入成功")
    except Exception as e:
        conn.rollback()
        print(f"插入聊天测试数据时出错: {e}")
    finally:
        cursor.close()
        conn.close()

def create_users_table():
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()
    
    # 先删除可能引用users表的表
    try:
        cursor.execute("DROP TABLE IF EXISTS feedback")
        cursor.execute("DROP TABLE IF EXISTS shops")
        cursor.execute("DROP TABLE IF EXISTS reports")
        cursor.execute("DROP TABLE IF EXISTS activity_participants")
        cursor.execute("DROP TABLE IF EXISTS comment_likes")
        cursor.execute("DROP TABLE IF EXISTS comment_replies")
        cursor.execute("DROP TABLE IF EXISTS comments")
        cursor.execute("DROP TABLE IF EXISTS activities")
        cursor.execute("DROP TABLE IF EXISTS friendships")
        cursor.execute("DROP TABLE IF EXISTS friend_requests")
        # 先删除可能存在的旧表
        cursor.execute("DROP TABLE IF EXISTS user_passwords")
        # 先删除可能存在的用户积分表
        cursor.execute("DROP TABLE IF EXISTS user_points_log")

        # 先删除旧表（如果存在）
        cursor.execute("DROP TABLE IF EXISTS payment_orders")

        # 先删除旧表（如果存在）
        cursor.execute("DROP TABLE IF EXISTS payment_config")
        
        # 先删除旧表（如果存在）
        cursor.execute("DROP TABLE IF EXISTS points_package")

        # 删除WebSocket聊天相关表
        cursor.execute("DROP TABLE IF EXISTS chat_sessions")
        cursor.execute("DROP TABLE IF EXISTS chat_messages")
        cursor.execute("DROP TABLE IF EXISTS user_online_status")
    

        print("已删除引用表")
    except Exception as e:
        print(f"删除引用表时出错: {e}")
    
    # 再删除users表
    cursor.execute("DROP TABLE IF EXISTS users")
    
    create_table_sql = """
    CREATE TABLE users (
        userid INT AUTO_INCREMENT PRIMARY KEY,  # 用户ID，自增
        userPhone VARCHAR(20) UNIQUE,           # 用户手机号
        userEmail VARCHAR(100) UNIQUE,          # 用户邮箱，添加UNIQUE约束确保唯一性
        userCode INT,                           # 用户商家码，0代表用户1代表商家，2代表用户被禁用
        username VARCHAR(50) NOT NULL,          # 用户名
        userAvatarUrl VARCHAR(255),             # 用户头像URL
        userData JSON,                          # 用户数据，JSON格式
        userAge INT,                            # 用户年龄
        userGender VARCHAR(10),                 # 用户性别
        userSignature TEXT,                     # 用户个性签名
        userTags JSON,                          # 用户标签，JSON数组
        userLikes INT DEFAULT 0,                # 用户获得的点赞数
        userFollowers INT DEFAULT 0,            # 用户粉丝数
        userPoints INT DEFAULT 0,               # 用户积分
        userHobbies JSON,                       # 用户爱好，JSON数组[00000,{"dianyng","chongwu"}]
        userCity VARCHAR(50),                   # 用户所在城市
        userImgUrls JSON,                       # 用户图片URL，JSON数组
        userFrequentPlaces VARCHAR(255),        # 用户常出没地
        userBirthday DATE,                      # 用户生日
        userLevel TINYINT DEFAULT 0,            # 用户级别：0-5
        status ENUM('开启', '关闭', '删除') DEFAULT '开启', # 新增：记录状态
        extraData JSON,                         # 新增：备用字段
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  # 创建时间
        last_login TIMESTAMP NULL,              # 最后登录时间
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, # 更新时间
        INDEX idx_phone (userPhone),            # 添加手机号索引
        INDEX idx_status (status)               # 添加状态索引
    )
    """
    cursor.execute(create_table_sql)
    conn.commit()
    print("users表创建成功")
    cursor.close()
    conn.close()


def create_user_password_table():
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()
    

    
    create_table_sql = """
    CREATE TABLE user_passwords (
        id INT AUTO_INCREMENT PRIMARY KEY,
        userid INT NOT NULL UNIQUE,  # 与users表的userid关联
        password_hash VARCHAR(255) NOT NULL,  # 存储加密后的密码
        salt VARCHAR(255) NOT NULL,  # 密码盐值
        algorithm VARCHAR(50) NOT NULL DEFAULT 'pbkdf2_sha256',  # 加密算法
        iterations INT NOT NULL DEFAULT 260000,  # 迭代次数
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  # 创建时间
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  # 更新时间
        last_changed TIMESTAMP NULL,  # 上次修改密码时间
        FOREIGN KEY (userid) REFERENCES users(userid) ON DELETE CASCADE,
        INDEX idx_userid (userid)
    )
    """
    cursor.execute(create_table_sql)
    conn.commit()
    print("user_passwords表创建成功")
    cursor.close()
    conn.close()




def create_user_points_table():
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()

    
    create_table_sql = """
    CREATE TABLE user_points_log (
        id INT AUTO_INCREMENT PRIMARY KEY,           # 记录ID，自增
        userid INT NOT NULL,                         # 用户ID，关联users表
        points_change INT NOT NULL,                  # 积分变动值（正数为增加，负数为减少）
        points_before INT NOT NULL,                  # 变动前积分
        points_after INT NOT NULL,                   # 变动后积分
        change_type TINYINT NOT NULL,                # 变动类型：1-签到，2-购物，3-活动奖励，4-积分兑换，5-管理员调整，6-其他
        change_reason VARCHAR(255),                  # 变动原因描述
        related_id VARCHAR(50),                      # 关联业务ID（如订单ID、活动ID等）
        operator VARCHAR(50),                        # 操作人（系统或管理员账号）
        remark TEXT,                                 # 备注信息
        status ENUM('有效', '无效', '已撤销') DEFAULT '有效', # 记录状态
        extraData JSON,                              # 备用字段，存储额外信息
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  # 创建时间
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, # 更新时间
        INDEX idx_userid (userid),                   # 用户ID索引
        INDEX idx_change_type (change_type),         # 变动类型索引
        INDEX idx_created_at (created_at),           # 创建时间索引
        FOREIGN KEY (userid) REFERENCES users(userid) ON DELETE CASCADE # 外键关联users表
    )
    """
    cursor.execute(create_table_sql)
    conn.commit()
    print("user_points_log表创建成功")
    cursor.close()
    conn.close()



def insert_sample_data():
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()
    
    # 更新数据 - 以userPhone为唯一标识
    sample_users = [
        # userPhone, userCode, username, userAvatarUrl, userData, userAge, userGender, 
        # userSignature, userTags, userLikes, userFollowers, userPoints, userHobbies,
        # userCity, userImgUrls, userFrequentPlaces, userBirthday, userLevel, created_at,
        # status, extraData
        (
            "13800138001", 123456, "Alice", "https://example.com/avatar1.jpg",
            '{"vip": true, "notes": "活跃用户"}', 28, "女",
            "热爱生活，享受每一天", '["文艺", "时尚", "科技"]', 
            120, 45, 200, '["阅读", "旅行", "摄影"]',
            "北京", '["https://example.com/img1.jpg", "https://example.com/img2.jpg"]',
            "咖啡厅，图书馆", "1995-05-15", 3, "2023-01-15 10:00:00",
            "开启", '{"兴趣爱好": "旅行摄影", "个人简介": "热爱生活的北京女孩"}'
        ),
        # ... 其他用户数据，添加status和extraData字段
        (
            "13900139002", 654321, "Bob", "https://example.com/avatar2.jpg",
            '{"vip": false, "notes": "新用户"}', 32, "男",
            "游戏爱好者，电竞达人", '["游戏", "电竞", "科技"]', 
            80, 30, 150, '["游戏", "编程", "篮球"]',
            "上海", '["https://example.com/img3.jpg"]',
            "网吧，篮球场", "1991-08-22", 2, "2023-02-20 14:30:00",
            "开启", '{"游戏ID": "BobTheGamer", "擅长游戏": ["LOL", "CSGO"]}'
        ),
        # ... 其他用户数据
        (
            "13700137003", 789012, "Charlie", "https://example.com/avatar3.jpg",
            '{"vip": true, "notes": "资深用户"}', 25, "男",
            "运动健将，永不止步", '["运动", "健身", "户外"]', 
            200, 100, 350, '["足球", "健身", "徒步"]',
            "广州", '["https://example.com/img4.jpg", "https://example.com/img5.jpg"]',
            "健身房，足球场", "1998-11-30", 4, "2023-03-10 09:15:00",
            "开启", '{"健身经验": "5年", "擅长项目": "足球、健身"}'
        ),
        (
            "13600136004", 345678, "David", "https://example.com/avatar4.jpg",
            '{"vip": false, "notes": "音乐爱好者"}', 30, "男",
            "音乐是我的生命", '["音乐", "艺术", "创作"]', 
            150, 60, 250, '["吉他", "钢琴", "作曲"]',
            "成都", '["https://example.com/img6.jpg"]',
            "音乐厅，录音室", "1993-04-18", 3, "2023-04-05 16:45:00",
            "开启", '{"擅长乐器": ["吉他", "钢琴"], "音乐风格": "民谣、摇滚"}'
        ),
        (
            "13500135005", 901234, "Eve", "https://example.com/avatar5.jpg",
            '{"vip": true, "notes": "旅行达人"}', 27, "女",
            "行走的地图，环球旅行者", '["旅行", "摄影", "美食"]', 
            300, 200, 500, '["旅行", "摄影", "品尝美食"]',
            "深圳", '["https://example.com/img7.jpg", "https://example.com/img8.jpg"]',
            "机场，景点", "1996-07-08", 5, "2023-05-12 11:20:00",
            "开启", '{"去过的国家": 15, "最喜欢的目的地": "日本、意大利"}'
        ),
        (
            "13400134006", 567890, "Frank", "https://example.com/avatar6.jpg",
            '{"vip": false, "notes": "美食家"}', 35, "男",
            "生活因美食而精彩", '["美食", "烹饪", "品鉴"]', 
            100, 40, 180, '["烹饪", "品酒", "美食探店"]',
            "杭州", '["https://example.com/img9.jpg"]',
            "餐厅，厨房", "1988-12-25", 2, "2023-06-18 13:10:00",
            "开启", '{"擅长菜系": "法餐、中餐", "厨师等级": "业余高级"}'
        )
    ]
    
    for user in sample_users:
        # 解构用户数据
        (userPhone, userCode, username, userAvatarUrl, userData, userAge, userGender,
         userSignature, userTags, userLikes, userFollowers, userPoints, userHobbies,
         userCity, userImgUrls, userFrequentPlaces, userBirthday, userLevel, created_at,
         status, extraData) = user
        
        update_sql = """
        INSERT INTO users 
        (userPhone, userCode, username, userAvatarUrl, userData, userAge, userGender,
         userSignature, userTags, userLikes, userFollowers, userPoints, userHobbies,
         userCity, userImgUrls, userFrequentPlaces, userBirthday, userLevel, created_at,
         status, extraData) 
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        ON DUPLICATE KEY UPDATE
            userCode = VALUES(userCode),
            username = VALUES(username),
            userAvatarUrl = VALUES(userAvatarUrl),
            userData = VALUES(userData),
            userAge = VALUES(userAge),
            userGender = VALUES(userGender),
            userSignature = VALUES(userSignature),
            userTags = VALUES(userTags),
            userLikes = VALUES(userLikes),
            userFollowers = VALUES(userFollowers),
            userPoints = VALUES(userPoints),
            userHobbies = VALUES(userHobbies),
            userCity = VALUES(userCity),
            userImgUrls = VALUES(userImgUrls),
            userFrequentPlaces = VALUES(userFrequentPlaces),
            userBirthday = VALUES(userBirthday),
            userLevel = VALUES(userLevel),
            created_at = VALUES(created_at),
            status = VALUES(status),
            extraData = VALUES(extraData)
        """
        cursor.execute(update_sql, user)
        print(f"已更新用户 {username} 的数据")
    
    conn.commit()
    print(f"操作完成，共处理 {len(sample_users)} 条数据")
    cursor.close()
    conn.close()




def create_activities_table():
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()
    
    # 先删除旧表（如果存在）
    cursor.execute("DROP TABLE IF EXISTS activities")
    
    create_table_sql = """
    CREATE TABLE activities (
        activityId INT AUTO_INCREMENT PRIMARY KEY,  # 活动ID，自增
        activityTitle VARCHAR(100) NOT NULL,        # 活动标题
        activityImages JSON,                        # 活动图片，JSON数组存储多张图片URL
        activityType JSON,                          # 活动性质，改为JSON类型
        activityTags JSON,                          # 活动标签，JSON数组
        activityDetails TEXT,                       # 活动详情
        activityLocation VARCHAR(100),              # 发布位置
        activityCoordinates POINT NOT NULL,         # 活动坐标（经纬度）- 添加NOT NULL约束
        activityPublishTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, # 活动开始时间
        activityCategory JSON,                      # 活动类别，改为JSON类型
        activityRestriction JSON,                   # 活动限制条件，改为JSON类型
        activityCurrentParticipants INT DEFAULT 0,   # 目前参与人数
        activityMaxParticipants INT NOT NULL,        # 活动允许参与总人数
        publisherId INT NOT NULL,                    # 发布者ID，关联users表
        publisherAvatar VARCHAR(255),                # 发布者头像
        publisherNickname VARCHAR(50) NOT NULL,      # 发布者昵称
        publisherSignature TEXT,                     # 发布者个性签名
        publisherTags JSON,                          # 发布者标签，JSON数组
        activityStatus JSON,                         # 活动状态，改为JSON类型
        status JSON,                                 # 记录状态，改为JSON类型
        extraData JSON,                              # 备用字段，存储可能需要的额外数据
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, # 创建时间
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, # 更新时间
        INDEX idx_publisher (publisherId),           # 添加发布者索引
        INDEX idx_location (activityLocation),       # 添加位置索引
        SPATIAL INDEX idx_coordinates (activityCoordinates) # 添加空间索引用于地理位置查询
    )
    """
    cursor.execute(create_table_sql)
    conn.commit()
    print("activities表创建成功")
    cursor.close()
    conn.close()








# 创建活动回复表
def create_activity_replies_table():
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()
    
    # 先删除旧表（如果存在）
    cursor.execute("DROP TABLE IF EXISTS activity_replies")
    
    create_table_sql = """
    CREATE TABLE activity_replies (
        replyId INT AUTO_INCREMENT PRIMARY KEY,      # 回复ID，自增
        activityId INT NOT NULL,                     # 关联的活动ID
        activityUserId INT NOT NULL,                 # 关联的活动用户ID
        replyContent TEXT NOT NULL,                  # 回复内容
        replyImages JSON,                            # 回复图片，JSON数组
        publisherId INT NOT NULL,                    # 发布者ID
        publisherAvatar VARCHAR(255),                # 发布者头像
        publisherNickname VARCHAR(50) NOT NULL,      # 发布者昵称
        replyTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, # 回复时间
        likeCount INT DEFAULT 0,                     # 点赞数
        parentReplyId INT DEFAULT NULL,              # 父回复ID，用于回复嵌套
        status JSON,                                 # 记录状态，改为JSON类型
        extraData JSON,                              # 备用字段，存储可能需要的额外数据
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, # 创建时间
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, # 更新时间
        INDEX idx_activity (activityId),             # 添加活动索引
        INDEX idx_publisher (publisherId),           # 添加发布者索引
        INDEX idx_parent (parentReplyId),            # 添加父回复索引
        FOREIGN KEY (activityId) REFERENCES activities(activityId) ON DELETE CASCADE
    )
    """
    cursor.execute(create_table_sql)
    conn.commit()
    print("activity_replies表创建成功")
    cursor.close()
    conn.close()








def insert_sample_activities():
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()
    
    # 先确保表存在
    create_activities_table()
    
    # 更新数据 - 以activityId为唯一标识
    sample_activities = [
        # activityTitle, activityImages, activityType, activityTags, activityDetails, 
        # activityLocation, activityCoordinates(X,Y), activityCategory, activityRestriction,
        # activityMaxParticipants, publisherId, publisherAvatar, publisherNickname,
        # publisherSignature, publisherTags, activityStatus, status, extraData
        (
            "周末爬山活动", 
            '["https://example.com/activity1_1.jpg", "https://example.com/activity1_2.jpg"]',
            '["AA"]',  # 改为JSON数组
            '["户外", "运动", "交友"]',
            "本周末组织爬山活动，欢迎热爱户外运动的朋友参加，请自备水和食物，我们将提供专业向导。",
            "武汉市黄鹤楼公园", 
            "POINT(114.3062 30.5454)",  # 经度,纬度
            '[0,0,1,0,0]',  # 改为JSON数组
            '["无限制"]',  # 改为JSON数组
            20,  # 最大参与人数
            1,  # 发布者ID
            "https://example.com/avatar1.jpg",
            "户外达人",
            "热爱生活，享受自然",
            '["户外", "摄影", "健身"]',
            '["进行中"]',  # 改为JSON数组
            '["开启"]',  # 改为JSON数组
            '{"备注": "活动有专业向导", "难度": "中等"}'
        ),
        (
            "品茶交友聚会", 
            '["https://example.com/activity2_1.jpg"]',
            '["我买单"]',  # 改为JSON数组
            '["茶艺", "交友", "文化"]',
            "周六下午在茶馆举办品茶活动，可以品尝各种名茶，同时交流茶文化知识，欢迎茶友参加。",
            "武汉市洪山区某茶馆", 
            "POINT(114.3426 30.5147)",
            '[0,1,0,0,0]',  # 改为JSON数组
            '["无限制"]',  # 改为JSON数组
            10,
            2,
            "https://example.com/avatar2.jpg",
            "茶艺师",
            "以茶会友，以茶养心",
            '["茶艺", "文化", "艺术"]',
            '["进行中"]',  # 改为JSON数组
            '["开启"]',  # 改为JSON数组
            '{"茶种类": ["龙井", "铁观音", "普洱"], "费用预算": "人均50元"}'
        ),
        (
            "美食街探店", 
            '["https://example.com/activity3_1.jpg", "https://example.com/activity3_2.jpg", "https://example.com/activity3_3.jpg"]',
            '["AA"]',  # 改为JSON数组
            '["美食", "探店", "社交"]',
            "周日一起去探索武汉最新的美食街，尝试各种特色小吃，分享美食体验。",
            "武汉市江汉路步行街", 
            "POINT(114.2982 30.5809)",
            '[0,0,0,0,0]',  # 改为JSON数组
            '["无限制"]',  # 改为JSON数组
            8,
            3,
            "https://example.com/avatar3.jpg",
            "吃货王",
            "生活不只有眼前的苟且，还有诗和远方的美食",
            '["美食", "旅行", "摄影"]',
            '["进行中"]',  # 改为JSON数组
            '["开启"]',  # 改为JSON数组
            '{"推荐店铺": ["老通城", "周黑鸭", "蔡林记"], "预算": "人均100元"}'
        ),
        (
            "女生专属购物日", 
            '["https://example.com/activity4_1.jpg"]',
            '["AA"]',  # 改为JSON数组
            '["购物", "时尚", "女生"]',
            "周六组织女生专属购物活动，一起逛商场，交流时尚心得，互相给购物建议。",
            "武汉市光谷广场", 
            "POINT(114.4085 30.5082)",
            '[0,0,0,0,1]',  # 改为JSON数组
            '["女"]',  # 改为JSON数组
            15,
            4,
            "https://example.com/avatar4.jpg",
            "时尚达人",
            "爱美是女生的天性",
            '["时尚", "美妆", "购物"]',
            '["进行中"]',  # 改为JSON数组
            '["开启"]',  # 改为JSON数组
            '{"购物中心": ["群光广场", "世界城"], "活动时长": "4小时"}'
        ),
        (
            "密室逃脱挑战", 
            '["https://example.com/activity5_1.jpg", "https://example.com/activity5_2.jpg"]',
            '["AA"]',  # 改为JSON数组
            '["游戏", "团队", "智力"]',
            "周末组队挑战最新密室逃脱游戏，需要团队合作和智力解谜，欢迎喜欢挑战的朋友参加。",
            "武汉市汉街", 
            "POINT(114.3359 30.5586)",
            '[0,0,0,1,0]',  # 改为JSON数组
            '["无限制"]',  # 改为JSON数组
            6,
            5,
            "https://example.com/avatar5.jpg",
            "解谜大师",
            "生活就是一场解谜游戏",
            '["游戏", "解谜", "团队"]',
            '["进行中"]',  # 改为JSON数组
            '["开启"]',  # 改为JSON数组
            '{"密室主题": "古埃及法老的诅咒", "难度": "高级", "预计时间": "90分钟"}'
        )
    ]
    
    for activity in sample_activities:
        # 解构活动数据
        (activityTitle, activityImages, activityType, activityTags, activityDetails, 
         activityLocation, activityCoordinates, activityCategory, activityRestriction,
         activityMaxParticipants, publisherId, publisherAvatar, publisherNickname,
         publisherSignature, publisherTags, activityStatus, status, extraData) = activity
        
        insert_sql = """
        INSERT INTO activities 
        (activityTitle, activityImages, activityType, activityTags, activityDetails, 
         activityLocation, activityCoordinates, activityCategory, activityRestriction,
         activityMaxParticipants, publisherId, publisherAvatar, publisherNickname,
         publisherSignature, publisherTags, activityStatus, status, extraData) 
        VALUES (%s, %s, %s, %s, %s, %s, ST_GeomFromText(%s), %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """
        cursor.execute(insert_sql, activity)
        print(f"已添加活动 '{activityTitle}'")
    
    conn.commit()
    print(f"操作完成，共处理 {len(sample_activities)} 条数据")
    cursor.close()
    conn.close()

# 创建活动参与者关联表
def create_activity_participants_table():
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()
    
    # 先删除旧表（如果存在）
    cursor.execute("DROP TABLE IF EXISTS activity_participants")
    
    create_table_sql = """
    CREATE TABLE activity_participants (
        id INT AUTO_INCREMENT PRIMARY KEY,
        activityId INT NOT NULL,                    # 活动ID
        userId INT NOT NULL,                        # 参与者ID
        joinTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, # 参与时间
        participantStatus JSON,                     # 参与状态，改为JSON类型
        status JSON,                                # 记录状态，改为JSON类型
        extraData JSON,                             # 备用字段，存储可能需要的额外数据
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, # 创建时间
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, # 更新时间
        UNIQUE KEY unique_participant (activityId, userId), # 确保一个用户只能参加一次同一活动
        INDEX idx_activity (activityId),            # 添加活动索引
        INDEX idx_user (userId),                    # 添加用户索引
        FOREIGN KEY (activityId) REFERENCES activities(activityId) ON DELETE CASCADE,
        FOREIGN KEY (userId) REFERENCES users(userid) ON DELETE CASCADE
    )
    """
    cursor.execute(create_table_sql)
    conn.commit()
    print("activity_participants表创建成功")
    cursor.close()
    conn.close()

# 插入活动参与者模拟数据
def insert_sample_activity_participants():
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()
    
    # 确保表存在
    create_activity_participants_table()
    
    # 模拟数据
    sample_participants = [
        # activityId, userId, joinTime, participantStatus, status, extraData
        (1, 2, "2023-07-15 10:30:00", '["已报名"]', '["开启"]', '{"备注": "需要登山鞋", "联系方式": "13900139002"}'),
        (1, 3, "2023-07-15 11:20:00", '["已报名"]', '["开启"]', '{"备注": "带了相机", "联系方式": "13700137003"}'),
        (1, 4, "2023-07-16 09:15:00", '["已参加"]', '["开启"]', '{"备注": "擅长户外活动", "联系方式": "13600136004"}'),
        (2, 1, "2023-07-17 14:30:00", '["已报名"]', '["开启"]', '{"备注": "喜欢铁观音", "联系方式": "13800138001"}'),
        (2, 5, "2023-07-17 16:45:00", '["已参加"]', '["开启"]', '{"备注": "对茶文化很感兴趣", "联系方式": "13500135005"}'),
        (3, 1, "2023-07-18 10:00:00", '["已报名"]', '["开启"]', '{"备注": "想尝试武汉小吃", "联系方式": "13800138001"}'),
        (3, 2, "2023-07-18 11:30:00", '["已参加"]', '["开启"]', '{"备注": "带朋友一起", "联系方式": "13900139002"}'),
        (3, 6, "2023-07-18 12:15:00", '["已报名"]', '["开启"]', '{"备注": "美食爱好者", "联系方式": "13400134006"}'),
        (4, 5, "2023-07-19 09:45:00", '["已报名"]', '["开启"]', '{"备注": "想买新衣服", "联系方式": "13500135005"}'),
        (5, 2, "2023-07-20 15:20:00", '["已报名"]', '["开启"]', '{"备注": "喜欢解谜游戏", "联系方式": "13900139002"}'),
        (5, 3, "2023-07-20 16:10:00", '["已参加"]', '["开启"]', '{"备注": "组队参加", "联系方式": "13700137003"}'),
        (5, 4, "2023-07-20 16:30:00", '["已取消"]', '["开启"]', '{"备注": "临时有事", "联系方式": "13600136004"}')
    ]
    
    for participant in sample_participants:
        # 解构数据
        (activityId, userId, joinTime, participantStatus, status, extraData) = participant
        
        insert_sql = """
        INSERT INTO activity_participants 
        (activityId, userId, joinTime, participantStatus, status, extraData) 
        VALUES (%s, %s, %s, %s, %s, %s)
        ON DUPLICATE KEY UPDATE
            joinTime = VALUES(joinTime),
            participantStatus = VALUES(participantStatus),
            status = VALUES(status),
            extraData = VALUES(extraData)
        """
        try:
            cursor.execute(insert_sql, participant)
            print(f"已添加用户 {userId} 参与活动 {activityId} 的记录")
        except Exception as e:
            print(f"添加用户 {userId} 参与活动 {activityId} 的记录失败: {e}")
    
    conn.commit()
    print(f"操作完成，共处理 {len(sample_participants)} 条活动参与记录")
    cursor.close()
    conn.close()



# 创建评论表
def create_comments_table():
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()
    
    # 先删除旧表（如果存在）
    cursor.execute("DROP TABLE IF EXISTS comments")
    
    create_table_sql = """
    CREATE TABLE comments (
        commentId INT AUTO_INCREMENT PRIMARY KEY,    # 评论ID，自增
        commentTitle VARCHAR(100) NOT NULL,          # 评论标题
        commentContent TEXT NOT NULL,                # 评论内容
        commentImages JSON,                          # 评论图片，JSON数组存储多张图片URL
        commentCategory JSON,                        # 评论类别，改为JSON类型
        commentTags JSON,                            # 评论标签，JSON数组
        commentLocation VARCHAR(100),                # 发布位置
        commentCoordinates POINT NOT NULL,           # 评论坐标（经纬度）- 添加NOT NULL约束
        commentPublishTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, # 发布时间
        shoppingImages JSON,                         # 购物小票图片，JSON数组
        publisherId INT NOT NULL,                    # 发布者ID，关联users表
        publisherAvatar VARCHAR(255),                # 发布者头像
        publisherNickname VARCHAR(50) NOT NULL,      # 发布者昵称
        publisherSignature TEXT,                     # 发布者个性签名
        publisherTags JSON,                          # 发布者标签，JSON数组
        publisherPoints INT DEFAULT 0,               # 发布者积分
        likeCount INT DEFAULT 0,                     # 点赞数
        commentCount INT DEFAULT 0,                  # 评论数
        shareCount INT DEFAULT 0,                    # 分享数
        status JSON,                                 # 记录状态，改为JSON类型
        extraData JSON,                              # 备用字段，存储可能需要的额外数据
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, # 创建时间
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, # 更新时间
        INDEX idx_publisher (publisherId),           # 添加发布者索引
        INDEX idx_location (commentLocation),        # 添加位置索引
        SPATIAL INDEX idx_coordinates (commentCoordinates) # 添加空间索引用于地理位置查询
    )
    """
    cursor.execute(create_table_sql)
    conn.commit()
    print("comments表创建成功")
    cursor.close()
    conn.close()


# 插入示例评论数据
def insert_sample_comments():
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()
    
    # 先确保表存在
    create_comments_table()
    
    # 示例评论数据
    sample_comments = [
        # commentTitle, commentContent, commentImages, commentCategory, commentTags,
        # commentLocation, commentCoordinates(X,Y), shoppingImages,
        # publisherId, publisherAvatar, publisherNickname, publisherSignature, publisherTags, publisherPoints,
        # status, extraData
        (
            "武汉最好吃的热干面",
            "今天去尝试了武汉最有名的热干面店，味道真的很正宗！面条劲道，调料香浓，价格也很实惠。强烈推荐给所有来武汉的朋友。",
            '["https://example.com/comment1_1.jpg", "https://example.com/comment1_2.jpg"]',
            '["吃"]',  # 改为JSON数组
            '["美食", "热干面", "武汉特色"]',
            "武汉市洪山区某热干面店",
            "POINT(114.3426 30.5147)",
            '["https://example.com/receipt1.jpg"]',
            1,
            "https://example.com/avatar1.jpg",
            "美食达人",
            "寻找世界各地的美食",
            '["美食", "旅行", "摄影"]',
            350,
            '["开启"]',  # 改为JSON数组
            '{"店名": "老李热干面", "人均消费": "15元", "推荐指数": 5}'
        ),
        (
            "光谷广场音乐喷泉太震撼了",
            "周末去光谷广场看了音乐喷泉表演，灯光和水的结合太美了，音乐也很动听。是一个很适合晚上散步的好地方。",
            '["https://example.com/comment2_1.jpg", "https://example.com/comment2_2.jpg", "https://example.com/comment2_3.jpg"]',
            '["玩"]',  # 改为JSON数组
            '["夜景", "喷泉", "音乐"]',
            "武汉市光谷广场",
            "POINT(114.4085 30.5082)",
            '[]',
            2,
            "https://example.com/avatar2.jpg",
            "夜游达人",
            "夜晚的城市更有魅力",
            '["摄影", "夜景", "城市探索"]',
            280,
            '["开启"]',  # 改为JSON数组
            '{"表演时间": "每晚8点", "最佳观赏位置": "广场北侧", "拍照建议": "使用三脚架"}'
        ),
        (
            "这家奶茶店的珍珠超Q弹",
            "发现了一家隐藏在小巷子里的奶茶店，他们家的珍珠是手工现做的，超级Q弹！奶茶的茶底也很香，不会太甜。",
            '["https://example.com/comment3_1.jpg"]',
            '["喝"]',  # 改为JSON数组
            '["奶茶", "珍珠", "下午茶"]',
            "武汉市江汉区某奶茶店",
            "POINT(114.2982 30.5809)",
            '["https://example.com/receipt3.jpg"]',
            3,
            "https://example.com/avatar3.jpg",
            "奶茶控",
            "一天不喝奶茶就浑身难受",
            '["奶茶", "甜食", "咖啡"]',
            180,
            '["开启"]',  # 改为JSON数组
            '{"店名": "茶语时光", "招牌饮品": "黑糖珍珠奶茶", "价格": "18元起"}'
        ),
        (
            "汉街新开的电影院体验超棒",
            "汉街新开了一家电影院，设备很新，音效超赞，座椅也很舒适。最重要的是人不多，看电影的体验特别好。",
            '["https://example.com/comment4_1.jpg", "https://example.com/comment4_2.jpg"]',
            '["乐"]',  # 改为JSON数组
            '["电影", "影院", "休闲"]',
            "武汉市汉街",
            "POINT(114.3359 30.5586)",
            '["https://example.com/receipt4.jpg"]',
            4,
            "https://example.com/avatar4.jpg",
            "影迷",
            "热爱电影的艺术",
            '["电影", "音乐", "艺术"]',
            220,
            '["开启"]',  # 改为JSON数组
            '{"影院名称": "星轶影城", "特色": "杜比全景声", "推荐座位": "G排中间"}'
        ),
        (
            "这家服装店的设计很有特色",
            "在武广发现了一家设计师品牌店，衣服设计很有特色，面料也很舒适。价格虽然不便宜，但质量和设计绝对值得。",
            '["https://example.com/comment5_1.jpg", "https://example.com/comment5_2.jpg"]',
            '["购"]',  # 改为JSON数组
            '["时尚", "服装", "设计师品牌"]',
            "武汉市武广商圈",
            "POINT(114.3062 30.5454)",
            '["https://example.com/receipt5_1.jpg", "https://example.com/receipt5_2.jpg"]',
            5,
            "https://example.com/avatar5.jpg",
            "时尚博主",
            "穿出自己的风格",
            '["时尚", "穿搭", "购物"]',
            400,
            '["开启"]',  # 改为JSON数组
            '{"店名": "MOOI设计师品牌", "价格区间": "300-1500元", "风格": "简约北欧风"}'
        )
    ]
    
    for comment in sample_comments:
        # 解构评论数据
        (commentTitle, commentContent, commentImages, commentCategory, commentTags,
         commentLocation, commentCoordinates, shoppingImages,
         publisherId, publisherAvatar, publisherNickname, publisherSignature, publisherTags, publisherPoints,
         status, extraData) = comment
        
        insert_sql = """
        INSERT INTO comments 
        (commentTitle, commentContent, commentImages, commentCategory, commentTags,
         commentLocation, commentCoordinates, shoppingImages,
         publisherId, publisherAvatar, publisherNickname, publisherSignature, publisherTags, publisherPoints,
         status, extraData) 
        VALUES (%s, %s, %s, %s, %s, %s, ST_GeomFromText(%s), %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """
        cursor.execute(insert_sql, comment)
        print(f"已添加评论 '{commentTitle}'")
    
    conn.commit()
    print(f"操作完成，共处理 {len(sample_comments)} 条评论数据")
    cursor.close()
    conn.close()

# 创建评论回复表
def create_comment_replies_table():
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()
    
    # 先删除旧表（如果存在）
    cursor.execute("DROP TABLE IF EXISTS comment_replies")
    
    create_table_sql = """
    CREATE TABLE comment_replies (
        replyId INT AUTO_INCREMENT PRIMARY KEY,      # 回复ID，自增
        commentId INT NOT NULL,                      # 关联的评论ID
        replyContent TEXT NOT NULL,                  # 回复内容
        replyImages JSON,                            # 回复图片，JSON数组
        publisherId INT NOT NULL,                    # 发布者ID
        publisherAvatar VARCHAR(255),                # 发布者头像
        publisherNickname VARCHAR(50) NOT NULL,      # 发布者昵称
        replyTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, # 回复时间
        likeCount INT DEFAULT 0,                     # 点赞数
        parentReplyId INT DEFAULT NULL,              # 父回复ID，用于回复嵌套
        status JSON,                                 # 记录状态，改为JSON类型
        extraData JSON,                              # 备用字段，存储可能需要的额外数据
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, # 创建时间
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, # 更新时间
        INDEX idx_comment (commentId),               # 添加评论索引
        INDEX idx_publisher (publisherId),           # 添加发布者索引
        INDEX idx_parent (parentReplyId),            # 添加父回复索引
        FOREIGN KEY (commentId) REFERENCES comments(commentId) ON DELETE CASCADE
    )
    """
    cursor.execute(create_table_sql)
    conn.commit()
    print("comment_replies表创建成功")
    cursor.close()
    conn.close()


# 创建评论点赞关系表
def create_comment_likes_table():
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()
    
    # 先删除旧表（如果存在）
    cursor.execute("DROP TABLE IF EXISTS comment_likes")
    
    create_table_sql = """
    CREATE TABLE comment_likes (
        likeId INT AUTO_INCREMENT PRIMARY KEY,      # 点赞ID，自增
        commentId INT NOT NULL,                     # 关联的评论ID
        userId INT NOT NULL,                        # 点赞用户ID
        likeTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, # 点赞时间
        status TINYINT DEFAULT 1,                   # 状态：1-有效，0-已取消 2-点踩
        extraData JSON,                             # 备用字段，存储可能需要的额外数据
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, # 创建时间
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, # 更新时间
        UNIQUE KEY unique_like (commentId, userId), # 确保一个用户只能点赞一次同一评论
        INDEX idx_comment (commentId),              # 添加评论索引
        INDEX idx_user (userId),                    # 添加用户索引
        FOREIGN KEY (commentId) REFERENCES comments(commentId) ON DELETE CASCADE,
        FOREIGN KEY (userId) REFERENCES users(userid) ON DELETE CASCADE
    )
    """
    cursor.execute(create_table_sql)
    conn.commit()
    print("comment_likes表创建成功")
    cursor.close()
    conn.close()




# 创建分享表
def create_shares_table():
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()
    
    # 先删除旧表（如果存在）
    cursor.execute("DROP TABLE IF EXISTS shares")
    
    create_table_sql = """
    CREATE TABLE shares (
        shareId INT AUTO_INCREMENT PRIMARY KEY,        # 分享ID，自增
        userId INT NOT NULL,                          # 分享用户ID
        shareTitle VARCHAR(255) NOT NULL,             # 分享标题
        contentType VARCHAR(50) NOT NULL,             # 内容类型：activity-活动，comment-评论
        contentTitle VARCHAR(255),                    # 内容标题
        contentText TEXT,                             # 内容文本
        imageUrls JSON,                               # 图片URLs，JSON数组格式
        userNickname VARCHAR(100),                    # 分享者昵称
        userGender VARCHAR(10),                       # 分享者性别
        userCity VARCHAR(100),                        # 分享者城市
        userAvatarUrl VARCHAR(500),                   # 分享者头像URL
        shareUrl VARCHAR(1000),                       # 生成的分享链接
        viewCount INT DEFAULT 0,                      # 查看次数
        status TINYINT DEFAULT 1,                     # 状态：1-有效，0-已删除
        extraData JSON,                               # 备用字段，存储可能需要的额外数据
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, # 创建时间
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, # 更新时间
        INDEX idx_user (userId),                      # 添加用户索引
        INDEX idx_content_type (contentType),         # 添加内容类型索引
        INDEX idx_status (status),                    # 添加状态索引
        INDEX idx_created_at (created_at),            # 添加创建时间索引
        FOREIGN KEY (userId) REFERENCES users(userid) ON DELETE CASCADE
    )
    """
    cursor.execute(create_table_sql)
    conn.commit()
    print("shares表创建成功")
    cursor.close()
    conn.close()



# 插入评论回复模拟数据
def insert_sample_comment_replies():
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()
    
    # 确保表存在
    create_comment_replies_table()
    
    # 模拟数据
    sample_replies = [
        # commentId, replyContent, replyImages, publisherId, publisherAvatar, publisherNickname, 
        # replyTime, likeCount, parentReplyId, status, extraData
        (1, "这家店我也去过，确实很好吃！他们家的卤蛋也很入味。", '["https://example.com/reply1_1.jpg"]', 
         2, "https://example.com/avatar2.jpg", "夜游达人", "2023-07-21 09:30:00", 15, None, '["开启"]', 
         '{"推荐指数": 5, "去过次数": 3}'),
        
        (1, "请问具体地址在哪里呢？想去尝尝。", '[]', 
         3, "https://example.com/avatar3.jpg", "奶茶控", "2023-07-21 10:15:00", 8, None, '["开启"]', 
         '{"是否收藏": true}'),
        
        (1, "在洪山区珞瑜路，靠近光谷广场那边，很好找的。", '["https://example.com/reply1_3.jpg"]', 
         1, "https://example.com/avatar1.jpg", "美食达人", "2023-07-21 10:30:00", 12, 2, '["开启"]', 
         '{"附加地图": true}'),
        
        (2, "我也去看过，确实很震撼！建议晚上8点去，人少一些。", '[]', 
         4, "https://example.com/avatar4.jpg", "影迷", "2023-07-22 18:45:00", 20, None, '["开启"]', 
         '{"最佳观赏时间": "晚上8点-9点"}'),
        
        (2, "请问表演时间是固定的吗？", '[]', 
         6, "https://example.com/avatar6.jpg", "Frank", "2023-07-22 19:20:00", 5, None, '["开启"]', 
         '{}'),
        
        (2, "是的，每晚7点、8点和9点各有一场，每场大约15分钟。", '[]', 
         2, "https://example.com/avatar2.jpg", "夜游达人", "2023-07-22 19:35:00", 10, 5, '["开启"]', 
         '{"表演时长": "15分钟"}'),
        
        (3, "这家店在哪里？我也想去尝尝！", '[]', 
         5, "https://example.com/avatar5.jpg", "时尚博主", "2023-07-23 14:10:00", 7, None, '["开启"]', 
         '{}'),
        
        (3, "江汉区解放大道附近的一条小巷子里，叫'茶语时光'，很小但很有特色。", '["https://example.com/reply3_2.jpg"]', 
         3, "https://example.com/avatar3.jpg", "奶茶控", "2023-07-23 14:30:00", 9, 7, '["开启"]', 
         '{"店铺评分": 4.8}'),
        
        (4, "最近正好想去看电影，这家影院环境怎么样？", '[]', 
         1, "https://example.com/avatar1.jpg", "美食达人", "2023-07-24 11:20:00", 6, None, '["开启"]', 
         '{}'),
        
        (4, "环境很好，座椅是那种可以半躺的，看长片也不会累。", '["https://example.com/reply4_2.jpg"]', 
         4, "https://example.com/avatar4.jpg", "影迷", "2023-07-24 11:45:00", 8, 9, '["开启"]', 
         '{"座椅舒适度": 5}'),
        
        (5, "价格确实有点贵，但质量真的很好，我买了一件外套穿了两年还跟新的一样。", '[]', 
         2, "https://example.com/avatar2.jpg", "夜游达人", "2023-07-25 16:15:00", 12, None, '["开启"]', 
         '{"购买商品": "羊毛外套", "使用感受": "很耐穿"}'),
        
        (5, "他们家的设计风格很独特，适合喜欢简约风格的人。", '[]', 
         3, "https://example.com/avatar3.jpg", "奶茶控", "2023-07-25 17:00:00", 9, None, '["开启"]', 
         '{"风格偏好": "简约北欧风"}')
    ]
    
    for reply in sample_replies:
        # 解构数据
        (commentId, replyContent, replyImages, publisherId, publisherAvatar, publisherNickname, 
         replyTime, likeCount, parentReplyId, status, extraData) = reply
        
        insert_sql = """
        INSERT INTO comment_replies 
        (commentId, replyContent, replyImages, publisherId, publisherAvatar, publisherNickname, 
         replyTime, likeCount, parentReplyId, status, extraData) 
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """
        try:
            cursor.execute(insert_sql, reply)
            print(f"已添加对评论 {commentId} 的回复")
        except Exception as e:
            print(f"添加对评论 {commentId} 的回复失败: {e}")
    
    conn.commit()
    print(f"操作完成，共处理 {len(sample_replies)} 条评论回复")
    cursor.close()
    conn.close()






# 创建好友关系表
def create_friendships_table():
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()
    
    # 先删除旧表（如果存在）
    cursor.execute("DROP TABLE IF EXISTS friendships")
    
    create_table_sql = """
    CREATE TABLE friendships (
        friendship_id INT AUTO_INCREMENT PRIMARY KEY,  # 好友关系ID，自增
        user1_id INT NOT NULL,                         # 用户1 ID 返回参数的ID
        user2_id INT NOT NULL,                         # 用户2 ID 传入参数的ID
        status TINYINT DEFAULT 1,                      # 状态：1-正常好友关系，2-特别关注，3-黑名单
        remark VARCHAR(50),                            # 好友备注
        group_id INT,                                  # 好友分组ID
        intimacy_score INT DEFAULT 0,                  # 亲密度分数
        last_interaction TIMESTAMP NULL,               # 最后互动时间
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, # 成为好友时间
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, # 更新时间
        UNIQUE KEY unique_friendship (user1_id, user2_id), # 确保两个用户之间只有一条好友关系记录
        INDEX idx_user1 (user1_id),                    # 添加用户1索引
        INDEX idx_user2 (user2_id),                    # 添加用户2索引
        INDEX idx_status (status),                     # 添加状态索引
        FOREIGN KEY (user1_id) REFERENCES users(userid) ON DELETE CASCADE,
        FOREIGN KEY (user2_id) REFERENCES users(userid) ON DELETE CASCADE
    )
    """
    cursor.execute(create_table_sql)
    conn.commit()
    print("friendships表创建成功")
    cursor.close()
    conn.close()

# 创建好友申请表
def create_friend_requests_table():
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()
    
    # 先删除旧表（如果存在）
    cursor.execute("DROP TABLE IF EXISTS friend_requests")
    
    create_table_sql = """
    CREATE TABLE friend_requests (
        request_id INT AUTO_INCREMENT PRIMARY KEY,     # 申请ID，自增
        sender_id INT NOT NULL,                        # 发送者ID
        receiver_id INT NOT NULL,                      # 接收者ID
        status TINYINT DEFAULT 1,                      # 状态：1-待处理，2-已拒绝，3-已通过，4-已过期
        message VARCHAR(255),                          # 申请消息
        source VARCHAR(50),                            # 来源：搜索、推荐、扫码等
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, # 申请时间
        expired_at TIMESTAMP,                          # 过期时间，默认created_at + 24小时
        processed_at TIMESTAMP NULL,                   # 处理时间
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, # 更新时间
        INDEX idx_sender (sender_id),                  # 添加发送者索引
        INDEX idx_receiver (receiver_id),              # 添加接收者索引
        INDEX idx_status (status),                     # 添加状态索引
        INDEX idx_expired (expired_at),                # 添加过期时间索引
        FOREIGN KEY (sender_id) REFERENCES users(userid) ON DELETE CASCADE,
        FOREIGN KEY (receiver_id) REFERENCES users(userid) ON DELETE CASCADE
    )
    """
    cursor.execute(create_table_sql)
    conn.commit()
    print("friend_requests表创建成功")
    cursor.close()
    conn.close()

# 插入好友关系模拟数据
def insert_sample_friendships():
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()
    
    # 确保表存在
    create_friendships_table()
    
    # 模拟数据
    sample_friendships = [
        # user1_id, user2_id, status, remark, group_id, intimacy_score, last_interaction, created_at
        (1, 2, 1, "游戏好友", 1, 85, "2023-07-10 15:30:00", "2023-01-20 09:15:00"),
        (1, 3, 1, "健身伙伴", 2, 120, "2023-07-12 18:45:00", "2023-02-05 14:20:00"),
        (1, 4, 2, "音乐人", 1, 65, "2023-07-08 20:10:00", "2023-03-15 11:30:00"),
        (2, 3, 1, "Charlie", 1, 50, "2023-07-11 12:00:00", "2023-04-10 16:45:00"),
        (2, 5, 1, "旅行达人", 2, 95, "2023-07-13 09:30:00", "2023-05-22 10:20:00"),
        (3, 6, 1, "美食家", 1, 70, "2023-07-09 13:15:00", "2023-06-18 17:40:00"),
        (4, 5, 3, "已拉黑", 3, 10, "2023-06-30 11:20:00", "2023-03-25 09:10:00"),
        (5, 6, 1, "Frank", 2, 60, "2023-07-14 16:50:00", "2023-05-30 15:25:00")
    ]
    
    for friendship in sample_friendships:
        # 解构好友关系数据
        (user1_id, user2_id, status, remark, group_id, intimacy_score, 
         last_interaction, created_at) = friendship
        
        insert_sql = """
        INSERT INTO friendships 
        (user1_id, user2_id, status, remark, group_id, intimacy_score, 
         last_interaction, created_at) 
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
        ON DUPLICATE KEY UPDATE
            status = VALUES(status),
            remark = VALUES(remark),
            group_id = VALUES(group_id),
            intimacy_score = VALUES(intimacy_score),
            last_interaction = VALUES(last_interaction)
        """
        cursor.execute(insert_sql, friendship)
        print(f"已添加好友关系：用户{user1_id} - 用户{user2_id}")
    
    conn.commit()
    print(f"操作完成，共处理 {len(sample_friendships)} 条好友关系数据")
    cursor.close()
    conn.close()

# 插入好友申请模拟数据
def insert_sample_friend_requests():
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()
    
    # 确保表存在
    create_friend_requests_table()
    
    # 模拟数据
    sample_requests = [
        # sender_id, receiver_id, status, message, source, created_at, expired_at, processed_at
        (1, 5, 3, "我是Alice，想加你为好友", "搜索", "2023-07-01 10:00:00", 
         "2023-07-02 10:00:00", "2023-07-01 15:30:00"),
        (2, 6, 2, "我是Bob，看到你的美食照片很感兴趣", "推荐", "2023-07-02 14:20:00", 
         "2023-07-03 14:20:00", "2023-07-02 18:45:00"),
        (3, 4, 1, "我是Charlie，喜欢你的音乐", "朋友推荐", "2023-07-15 09:30:00", 
         "2023-07-16 09:30:00", None),
        (4, 1, 4, "我是David，想和你交流音乐", "搜索", "2023-07-10 16:40:00", 
         "2023-07-11 16:40:00", None),
        (5, 2, 3, "我是Eve，一起去旅行吧", "附近的人", "2023-07-05 11:20:00", 
         "2023-07-06 11:20:00", "2023-07-05 13:10:00"),
        (6, 3, 1, "我是Frank，看到你喜欢健身", "群聊", "2023-07-15 15:50:00", 
         "2023-07-16 15:50:00", None)
    ]
    
    for request in sample_requests:
        # 解构好友申请数据
        (sender_id, receiver_id, status, message, source, created_at, 
         expired_at, processed_at) = request
        
        insert_sql = """
        INSERT INTO friend_requests 
        (sender_id, receiver_id, status, message, source, created_at, 
         expired_at, processed_at) 
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
        """
        cursor.execute(insert_sql, request)
        print(f"已添加好友申请：用户{sender_id} -> 用户{receiver_id}")
    
    conn.commit()
    print(f"操作完成，共处理 {len(sample_requests)} 条好友申请数据")
    cursor.close()
    conn.close()








# 创建举报表
def create_reports_table():
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()
    
    # 先删除旧表（如果存在）
    cursor.execute("DROP TABLE IF EXISTS reports")
    
    create_table_sql = """
    CREATE TABLE reports (
        reportId INT AUTO_INCREMENT PRIMARY KEY,    # 举报ID，自增
        reportType VARCHAR(50) NOT NULL,            # 举报类型（评论、用户、活动等）
        targetId INT NOT NULL,                      # 被举报对象ID
        reportReason TEXT NOT NULL,                 # 举报原因
        reportImages JSON,                          # 举报证据图片，JSON数组
        reportCategory JSON,                        # 举报类别，JSON类型
        reportTags JSON,                            # 举报标签，JSON数组
        reportLocation VARCHAR(100),                # 举报发生位置
        reportCoordinates POINT,                    # 举报坐标（经纬度）
        reportTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, # 举报时间
        reporterId INT NOT NULL,                    # 举报者ID，关联users表
        reporterPhone VARCHAR(20),                  # 举报者手机号
        reporterUsername VARCHAR(50) NOT NULL,      # 举报者用户名
        reporterAvatar VARCHAR(255),                # 举报者头像
        targetUserId INT,                           # 被举报用户ID
        targetUsername VARCHAR(50),                 # 被举报用户名
        targetAvatar VARCHAR(255),                  # 被举报用户头像
        processStatus TINYINT DEFAULT 0,            # 处理状态：0-未处理，1-处理中，2-已处理，3-已驳回
        processResult TEXT,                         # 处理结果
        processTime TIMESTAMP NULL,                 # 处理时间
        processorId INT,                            # 处理人员ID
        status JSON,                                # 记录状态，JSON类型
        extraData JSON,                             # 备用字段，存储可能需要的额外数据
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, # 创建时间
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, # 更新时间
        INDEX idx_reporter (reporterId),            # 添加举报者索引
        INDEX idx_target (targetId),                # 添加被举报对象索引
        INDEX idx_target_user (targetUserId),       # 添加被举报用户索引
        INDEX idx_status (processStatus),           # 添加处理状态索引
        INDEX idx_location (reportLocation)         # 添加位置索引
    )
    """
    cursor.execute(create_table_sql)
    conn.commit()
    print("reports表创建成功")
    cursor.close()
    conn.close()



# 创建帮助与反馈表
def create_feedback_table():
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()
    
    # 先删除旧表（如果存在）
    cursor.execute("DROP TABLE IF EXISTS feedback")
    
    create_table_sql = """
    CREATE TABLE feedback (
        feedbackId INT AUTO_INCREMENT PRIMARY KEY,  # 反馈ID，自增
        feedbackType VARCHAR(50) NOT NULL,          # 反馈类型（问题反馈、功能建议、投诉等）
        feedbackTitle VARCHAR(100) NOT NULL,        # 反馈标题
        feedbackContent TEXT NOT NULL,              # 反馈内容
        feedbackImages JSON,                        # 反馈附带图片，JSON数组
        feedbackCategory JSON,                      # 反馈类别，JSON类型
        feedbackTags JSON,                          # 反馈标签，JSON数组
        feedbackLocation VARCHAR(100),              # 反馈发生位置
        feedbackCoordinates POINT,                  # 反馈坐标（经纬度）
        feedbackTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, # 反馈时间
        userId INT NOT NULL,                        # 用户ID，关联users表
        userPhone VARCHAR(20),                      # 用户手机号
        username VARCHAR(50) NOT NULL,              # 用户名
        userAvatar VARCHAR(255),                    # 用户头像
        contactInfo VARCHAR(100),                   # 联系方式（可选）
        appVersion VARCHAR(20),                     # 应用版本
        deviceInfo JSON,                            # 设备信息，JSON类型
        processStatus TINYINT DEFAULT 0,            # 处理状态：0-未处理，1-处理中，2-已处理，3-已回复
        processResult TEXT,                         # 处理结果
        processTime TIMESTAMP NULL,                 # 处理时间
        processorId INT,                            # 处理人员ID
        processorName VARCHAR(50),                  # 处理人员姓名
        satisfaction TINYINT,                       # 满意度评分（1-5）
        satisfactionComment TEXT,                   # 满意度评价
        status JSON,                                # 记录状态，JSON类型
        extraData JSON,                             # 备用字段，存储可能需要的额外数据
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, # 创建时间
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, # 更新时间
        INDEX idx_user (userId),                    # 添加用户索引
        INDEX idx_status (processStatus),           # 添加处理状态索引
        INDEX idx_type (feedbackType),              # 添加反馈类型索引
        INDEX idx_time (feedbackTime),              # 添加反馈时间索引
        INDEX idx_location (feedbackLocation)       # 添加位置索引
    )
    """
    cursor.execute(create_table_sql)
    conn.commit()
    print("feedback表创建成功")
    cursor.close()
    conn.close()

# 插入帮助与反馈示例数据
def insert_sample_feedback():
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()
    
    # 确保表存在
    create_feedback_table()
    
    # 模拟数据
    sample_feedback = [
        # feedbackType, feedbackTitle, feedbackContent, feedbackImages, feedbackCategory,
        # feedbackTags, feedbackLocation, feedbackCoordinates, userId, userPhone,
        # username, userAvatar, contactInfo, appVersion, deviceInfo,
        # processStatus, status, extraData
        (
            "问题反馈", 
            "应用闪退问题", 
            "在使用搜索功能时应用经常闪退，请修复这个问题。",
            '["https://example.com/feedback_img1.jpg"]',
            '["技术问题"]',
            '["闪退", "搜索功能"]',
            "武汉市洪山区", 
            "POINT(114.3426 30.5147)",
            1,
            "13800138001",
            "Alice",
            "https://example.com/avatar1.jpg",
            "13800138001",
            "1.2.3",
            '{"设备型号": "iPhone 13", "系统版本": "iOS 16.5"}',
            0,
            '["开启"]',
            '{"重现步骤": "1.打开应用 2.点击搜索 3.输入关键词"}'
        ),
        (
            "功能建议", 
            "增加黑夜模式", 
            "希望能增加黑夜模式，保护眼睛，提升用户体验。",
            '[]',
            '["功能优化"]',
            '["UI", "用户体验"]',
            "上海市浦东新区", 
            "POINT(121.5374 31.2245)",
            2,
            "13900139002",
            "Bob",
            "https://example.com/avatar2.jpg",
            "bob@example.com",
            "1.2.3",
            '{"设备型号": "小米12", "系统版本": "Android 13"}',
            1,
            '["开启"]',
            '{}'
        ),
        (
            "投诉", 
            "客服态度问题", 
            "客服回复太慢，态度也不好，希望改进服务质量。",
            '["https://example.com/feedback_img2.jpg", "https://example.com/feedback_img3.jpg"]',
            '["服务问题"]',
            '["客服", "服务质量"]',
            "广州市天河区", 
            "POINT(113.3345 23.1359)",
            3,
            "13700137003",
            "Charlie",
            "https://example.com/avatar3.jpg",
            "13700137003",
            "1.2.2",
            '{"设备型号": "华为P40", "系统版本": "EMUI 12"}',
            2,
            '["开启"]',
            '{"客服ID": "KF20230715", "会话时间": "2023-07-15 14:30:00"}'
        )
    ]
    
    for feedback in sample_feedback:
        # 解构反馈数据
        (feedbackType, feedbackTitle, feedbackContent, feedbackImages, feedbackCategory,
         feedbackTags, feedbackLocation, feedbackCoordinates, userId, userPhone,
         username, userAvatar, contactInfo, appVersion, deviceInfo,
         processStatus, status, extraData) = feedback
        
        insert_sql = """
        INSERT INTO feedback 
        (feedbackType, feedbackTitle, feedbackContent, feedbackImages, feedbackCategory,
         feedbackTags, feedbackLocation, feedbackCoordinates, userId, userPhone,
         username, userAvatar, contactInfo, appVersion, deviceInfo,
         processStatus, status, extraData) 
        VALUES (%s, %s, %s, %s, %s, %s, %s, ST_GeomFromText(%s), %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """
        cursor.execute(insert_sql, feedback)
        print(f"已添加反馈 '{feedbackTitle}'")
    
    conn.commit()
    print(f"操作完成，共处理 {len(sample_feedback)} 条数据")
    cursor.close()
    conn.close()

# 创建店铺表
def create_shops_table():
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()
    
    # 先删除旧表（如果存在）
    cursor.execute("DROP TABLE IF EXISTS shops")
    
    create_table_sql = """
    CREATE TABLE shops (
        shopId INT AUTO_INCREMENT PRIMARY KEY,     # 店铺ID，自增
        shopName VARCHAR(100) NOT NULL,            # 店铺名称
        shopType VARCHAR(50) NOT NULL,             # 店铺类型（餐饮、零售、服务等）
        shopDescription TEXT,                      # 店铺描述
        shopImages JSON,                           # 店铺图片，JSON数组
        shopCategory JSON,                         # 店铺类别，JSON类型
        shopTags JSON,                             # 店铺标签，JSON数组
        shopLocation VARCHAR(100),                 # 店铺位置
        shopCoordinates POINT,                     # 店铺坐标（经纬度）
        shopOpenTime VARCHAR(100),                 # 营业时间
        shopPhone VARCHAR(20),                     # 联系电话
        shopAddress VARCHAR(255),                  # 详细地址
        ownerId INT NOT NULL,                      # 店主ID，关联users表
        ownerPhone VARCHAR(20),                    # 店主手机号
        ownerName VARCHAR(50) NOT NULL,            # 店主用户名
        shopRating DECIMAL(2,1) DEFAULT 5.0,       # 店铺评分（1-5分）
        shopCommentIds JSON,                       # 店铺评论ID列表，JSON数组
        shopCommentCount INT DEFAULT 0,            # 店铺评论数量
        shopGoodReviews INT DEFAULT 0,             # 好评数量
        shopBadReviews INT DEFAULT 0,              # 差评数量
        shopActivityIds JSON,                      # 店铺活动ID列表，JSON数组
        shopLikeCount INT DEFAULT 0,               # 店铺点赞数量
        shopLikeIds JSON,                          # 店铺点赞用户ID列表，JSON数组
        shopCheckInCount INT DEFAULT 0,            # 店铺打卡数量
        shopCheckInIds JSON,                       # 店铺打卡记录ID列表，JSON数组
        businessLicense VARCHAR(100),              # 营业执照号
        shopStatus TINYINT DEFAULT 1,              # 店铺状态：1-营业中，2-休息中，3-已关闭
        verifyStatus TINYINT DEFAULT 0,            # 认证状态：0-未认证，1-认证中，2-已认证
        shopFollowers INT DEFAULT 0,               # 店铺粉丝数
        shopViews INT DEFAULT 0,                   # 店铺浏览量
        shopSales INT DEFAULT 0,                   # 店铺销量
        shopLevel TINYINT DEFAULT 1,               # 店铺等级
        shopNotice TEXT,                           # 店铺公告
        shopFeatures JSON,                         # 店铺特色，JSON数组
        shopPromotions JSON,                       # 店铺促销活动，JSON数组
        status JSON,                               # 记录状态，JSON类型
        extraData JSON,                            # 备用字段，存储可能需要的额外数据
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, # 创建时间
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, # 更新时间
        INDEX idx_owner (ownerId),                 # 添加店主索引
        INDEX idx_name (shopName),                 # 添加店铺名称索引
        INDEX idx_type (shopType),                 # 添加店铺类型索引
        INDEX idx_location (shopLocation),         # 添加位置索引
        INDEX idx_status (shopStatus),             # 添加店铺状态索引
        INDEX idx_verify (verifyStatus)            # 添加认证状态索引
    )
    """
    cursor.execute(create_table_sql)
    conn.commit()
    print("shops表创建成功")
    cursor.close()
    conn.close()









# 创建支付订单表
def create_payment_orders_table():
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()
    
    # 先删除旧表（如果存在）
    cursor.execute("DROP TABLE IF EXISTS payment_orders")
    
    create_table_sql = """
    CREATE TABLE payment_orders (
        order_id VARCHAR(64) PRIMARY KEY,           # 订单ID，格式：年月日时分秒+随机数
        user_id INT NOT NULL,                       # 关联用户ID
        amount DECIMAL(10,2) NOT NULL,              # 支付金额
        points INT NOT NULL,                        # 购买积分数量
        order_title VARCHAR(128) NOT NULL,          # 订单标题
        order_description TEXT,                     # 订单描述
        payment_platform VARCHAR(20) NOT NULL,      # 支付平台（alipay, wechat等）
        alipay_trade_no VARCHAR(64),                # 支付宝交易号
        alipay_app_id VARCHAR(32),                  # 支付宝应用ID
        alipay_out_trade_no VARCHAR(64),            # 支付宝商户订单号
        payment_status TINYINT DEFAULT 0,           # 支付状态：0-待支付，1-已支付，2-支付失败，3-已退款，4-已关闭
        notify_data JSON,                           # 支付平台回调数据
        notify_time TIMESTAMP NULL,                 # 通知时间
        client_ip VARCHAR(50),                      # 客户端IP
        user_agent TEXT,                            # 用户浏览器信息
        expire_time TIMESTAMP,                      # 订单过期时间
        payment_time TIMESTAMP NULL,                # 支付时间
        refund_time TIMESTAMP NULL,                 # 退款时间
        refund_amount DECIMAL(10,2),                # 退款金额
        refund_reason TEXT,                         # 退款原因
        operator VARCHAR(50),                       # 操作人（系统或管理员账号）
        extraData JSON,                             # 备用字段，存储额外信息
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  # 创建时间
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, # 更新时间
        INDEX idx_user_id (user_id),                # 用户ID索引
        INDEX idx_payment_status (payment_status),  # 支付状态索引
        INDEX idx_created_at (created_at),          # 创建时间索引
        INDEX idx_alipay_trade_no (alipay_trade_no),  # 支付宝交易号索引
        INDEX idx_alipay_out_trade_no (alipay_out_trade_no),  # 支付宝商户订单号索引
        FOREIGN KEY (user_id) REFERENCES users(userid) ON DELETE CASCADE # 外键关联users表
    )
    """
    cursor.execute(create_table_sql)
    conn.commit()
    print("payment_orders表创建成功")
    cursor.close()
    conn.close()

# 创建支付配置表
def create_payment_config_table():
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()
    
    # 先删除旧表（如果存在）
    cursor.execute("DROP TABLE IF EXISTS payment_config")
    
    create_table_sql = """
    CREATE TABLE payment_config (
        id INT AUTO_INCREMENT PRIMARY KEY,
        platform VARCHAR(20) NOT NULL,              # 支付平台（alipay, wechat等）
        config_name VARCHAR(50) NOT NULL,           # 配置名称
        config_value TEXT NOT NULL,                 # 配置值
        config_description TEXT,                    # 配置描述
        is_active TINYINT DEFAULT 1,                # 是否激活：1-激活，0-未激活
        environment VARCHAR(10) DEFAULT 'prod',     # 环境：dev-开发环境，test-测试环境，prod-生产环境
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  # 创建时间
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, # 更新时间
        UNIQUE KEY unique_config (platform, config_name, environment) # 确保每个环境下，每个平台的配置名唯一
    )
    """
    cursor.execute(create_table_sql)
    
    # 插入支付宝配置数据 - 使用正确的PEM格式
    app_private_key = """-----BEGIN RSA PRIVATE KEY-----
MIIEuwIBADANBgkqhkiG9w0BAQEFAASCBKUwggShAgEAAoIBAQCcKTY8a4vAP0Bk
aa95LeKy0G+5mpNW5JDtD83ESxuP0xnKtmCKPG3kSHvG9oo89r4arv6xXUSV54A5
jVtCb5G0u4vjGLckSgF7PkVAdOxSQOMNbPiUrwnwDSwCJkuoNjn1YT9bhivkEl5v
FgDD48XLunuMZ/UprRInGrjHiqwkF/EmHdfvh7Wz0KvSWSsBVQR1RlHK+c21sAtC
zZt1kUsFeBK1p0GjI90DH+tClh+1rjKThOPFNtPdBfGGV3Jnvv2sMnHvbDwN9SSX
Wm0vE+TxOK3s+bflxGIbJHMGsxiZpr1wB5khk26qk3jhhLrNlho4g8ryflj2f7SX
2ZYhpJo7AgMBAAECggEBAINgqKBsWkaC8EmKIHZyhCar9VLRGOQhp0vJ3/MkvStj
Dm/cv5uM+2bzFC9vex2O+KC6WMwfC3SSLu48XmqmMo86uZV1VZ7SJCzrVpfDxo3s
yRiidP+hw1qRulSUo1IBTQGDrynw1zwpuAj9Geez2NRtL0QQsdE+Ked8M7iskeZK
aIVck8zVJIpUqxVkd/GlfsEnmWZBpoOy2I26V6wu77WPmokKTiw2iOzXksLIulKz
vcyvc4eOE4ZS64O/WCHbBRtecDo1pyScNpbVA9MD4Tu80y/j7xUJMSN+KY0KNOmF
TVT5UAxwa1+fQszhMGGtv3UKVw/phU087q+JSpFm7gECgYEA7pbcExMDiIj5kGmd
F2SI/bBMq/HgKIYd8xmfGkZTjg+1WxWBfDU2/V4CNJzCqGZ9bl8jBBW5Y9vnCVyJ
vIFDv1Uz7iTCwwWhkecuzDgxtsI4hJk6+xZlQNIarikzx9dSPy78x/hzKb6PkONw
KGlmctKL0W2m9K4wU9gfBVKfgEMCgYEAp459h3R4W6y6oRUpXj6cQblbSfZkni2F
e46SnwS3l1lPpKRJBmbrljtTgCVTs81EmWmDAXRZNRKffh80P6Bph8gxuGwjmQDh
yNSqsBUCU+yYYnh8LekcidNLFTGmO93YBvjzd4qXUXoBqpLJ2YwDhVxqBsTgNTnj
O2XgVPTTeqkCgYANVdNBIavt4rIIN073csARZdwzNBayTSS5ZLI21r1u0EPzrlUJ
azQCn1GCPUzH+sqB88FSRiCRi0XziP+N0K/HruxzIAzet57bN8+uzGlC7MIZ7zKT
vCG2qMei+lyRi+Bl9atdRe6x45vEnmGzcIiwXEJCV12giWfcF9VMB90wXwKBgA3e
DSWYzh1IPmn2hmX2aJhH7vZAHjO5MML5GclvaoJsMds+fYy6F+VnI5IHhNXstzC7
Osx0BK4PzINzsa9SZ2mFeBf7Wzbei9FDSJ0qH/cyLap4rk5ebK47fotSgJ+TzL1G
/V93aO6lWl3zqCEJJYF0FJJa2POOxu+zjs15NceBAn8GafNJdUJT2NqiAZqvxebD
VdfUWrRFsJmjCKrvBTZeIgnge4FEv5jVfr+USPczROXd7vnRHoC1OpI1TVHeUfvJ
f1fNkkwAv+Mvmlv9F5G9QtdZEPjMKdXy8otH3Sp1QszRaPVDq0wjO6iVPAOtg3m+
E52I/GY1shUrQMST0W2I
-----END RSA PRIVATE KEY-----"""

    alipay_public_key = """-----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAnCk2PGuLwD9AZGmveS3i
stBvuZqTVuSQ7Q/NxEsbj9MZyrZgijxt5Eh7xvaKPPa+Gq7+sV1EleeAOY1bQm+R
tLuL4xi3JEoBez5FQHTsUkDjDWz4lK8J8A0sAiZLqDY59WE/W4Yr5BJebxYAw+PF
y7p7jGf1Ka0SJxq4x4qsJBfxJh3X74e1s9Cr0lkrAVUEdUZRyvnNtbALQs2bdZFL
BXgStadBoyPdAx/rQpYfta4yk4TjxTbT3QXxhldyZ779rDJx72w8DfUkl1ptLxPk
8Tit7Pm35cRiGyRzBrMYmaa9cAeZIZNuqpN44YS6zZYaOIPK8n5Y9n+0l9mWIaSa
OwIDAQAB
-----END PUBLIC KEY-----"""
    
    insert_config_sql = """
    INSERT INTO payment_config 
    (platform, config_name, config_value, config_description, is_active, environment) 
    VALUES 
    ('alipay', 'app_id', '2021005150647183', '支付宝应用ID', 1, 'dev'),
    (%s, %s, %s, %s, %s, %s),
    (%s, %s, %s, %s, %s, %s),
    ('alipay', 'notify_url', 'http://app.subeplayreal.cn:1120/api/payment/alipay/notify/', '支付宝异步通知地址', 1, 'dev'),
    ('alipay', 'return_url', 'http://app.subeplayreal.cn:1120/api/payment/alipay/return/', '支付宝同步返回地址', 1, 'dev')
    """
    
    cursor.execute(insert_config_sql, ('alipay', 'app_private_key', app_private_key, '应用私钥', 1, 'dev',
                                      'alipay', 'alipay_public_key', alipay_public_key, '支付宝公钥', 1, 'dev'))
    
    conn.commit()
    print("payment_config表创建成功并插入初始配置")
    cursor.close()
    conn.close()

# 创建积分套餐表
def create_points_package_table():
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()
    
    # 先删除旧表（如果存在）
    cursor.execute("DROP TABLE IF EXISTS points_package")
    
    create_table_sql = """
    CREATE TABLE points_package (
        package_id INT AUTO_INCREMENT PRIMARY KEY,
        package_name VARCHAR(50) NOT NULL,          # 套餐名称
        points_amount INT NOT NULL,                 # 积分数量
        price DECIMAL(10,2) NOT NULL,               # 价格
        discount DECIMAL(5,2) DEFAULT 0,            # 折扣
        original_price DECIMAL(10,2),               # 原价
        package_description TEXT,                   # 套餐描述
        is_popular TINYINT DEFAULT 0,               # 是否热门：1-是，0-否
        is_active TINYINT DEFAULT 1,                # 是否激活：1-激活，0-未激活
        sort_order INT DEFAULT 0,                   # 排序顺序
        icon_url VARCHAR(255),                      # 图标URL
        extraData JSON,                             # 备用字段
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  # 创建时间
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, # 更新时间
        INDEX idx_is_active (is_active),            # 是否激活索引
        INDEX idx_sort_order (sort_order)           # 排序顺序索引
    )
    """
    cursor.execute(create_table_sql)
    
    # 插入默认套餐数据
    insert_packages_sql = """
    INSERT INTO points_package 
    (package_name, points_amount, price, discount, original_price, package_description, is_popular, is_active, sort_order, icon_url) 
    VALUES 
    ('积分100', 100, 10.00, 0, 10.00, '入门积分礼包', 0, 1, 1, ''),
    ('积分300', 300, 29.00, 3.33, 30.00, '普通积分礼包', 0, 1, 2, ''),
    ('积分500', 500, 45.00, 10.00, 50.00, '超值积分礼包', 1, 1, 3, ''),
    ('积分1200', 1200, 99.00, 17.50, 120.00, '至尊积分礼包', 0, 1, 4, '')
    """
    cursor.execute(insert_packages_sql)
    
    conn.commit()
    print("points_package表创建成功并插入初始数据")
    cursor.close()
    conn.close()











if __name__ == "__main__":






    # # 执行测试  
    # test_connection()

    # # 修改执行顺序，确保先创建基础表，再创建引用表
    # # 1. 先创建用户表



    # create_users_table()
    # # insert_sample_data()

    # # 2. 再创建活动表
    # create_activities_table()
    # # insert_sample_activities()

    # # 3. 然后创建评论表
    # create_comments_table()
    # # insert_sample_comments()

    # # 4. 最后创建关联表和回复表
    # create_activity_participants_table()
    # # insert_sample_activity_participants()

    # # 创建评论点赞关系表
    # create_comment_likes_table()


    # create_comment_replies_table()
    # # insert_sample_comment_replies()


    # # 创建好友关系表和好友申请表并插入示例数据
    # create_friendships_table()
    # # insert_sample_friendships()

    # create_friend_requests_table()
    # # insert_sample_friend_requests()


    # # 创建举报表
    # create_reports_table()

    # # 创建帮助与反馈表
    # create_feedback_table()


    # # 创建店铺表
    # create_shops_table()

    # # 创建用户积分表
    # create_user_points_table()


    # # 创建用户密码表
    # create_user_password_table()


    # #创建支付配置表        
    # create_payment_config_table()


    # #创建积分套餐表
    # create_points_package_table()


    # #创建支付订单表
    # create_payment_orders_table()

    # # 创建活动回复表
    create_activity_replies_table()

    # 创建分享表
    # create_shares_table()


    #创建WebSocket聊天相关表
    # create_chat_tables()

    print("所有表创建完成！")
